﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using CsvHelper;
using Dapper;
using FinHist.Data;
using Linq.Csv;
using System.Threading;

namespace FinHist.StockQuotes
{
    class Program
    {
        static void Main()
        {
            var connString = ConfigurationManager.ConnectionStrings["trading"].ConnectionString;

            var endDate = DateTime.Today;
            var startDate = new DateTime(2009, 1, 1);

            var sampleStock = 
                syncAsync(new Stock() { code = "600000", companyName = "Pufa bank", exchange = "ss", yahooId = "600000.ss" }, DateTime.Today.AddDays(-7), DateTime.Today.AddDays(1))
                .Result;

            using (var conn = new SqlConnection(connString))
            {
                conn.Open();

                var sample =
                    conn.Query<dynamic>(@"select code, exchange, MIN(date) minDate, MAX(date) maxDate from hist where code='600000' and exchange='ss' group by code, exchange");

                endDate = sample.Max(p => p.maxDate);
            }

            //get all stocks
            IEnumerable<dynamic> stocksWithDate;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();

                var stocks =
                    conn.Query<Stock>(@"select * from stock");

                var filter =
                    conn.Query<dynamic>(@"select code, exchange, MIN(date) minDate, MAX(date) maxDate from hist group by code, exchange");

                stocksWithDate = stocks
                    .Select(p =>
                        {
                            var singleEndDate = endDate;
                            var singleStartDate = startDate;

                            var single = filter.SingleOrDefault(p1 => p1.code == p.code && p1.exchange == p.exchange);
                            if (single != null)
                                singleStartDate = single.maxDate;

                            return new { Stock = p, startDate = singleStartDate, endDate = singleEndDate };
                        })
                    .Where(p => p.endDate > p.startDate)
                    .ToArray();
            }

            var sw = new Stopwatch();
            sw.Start();
            Console.WriteLine("start @ {0:HH:mm:ss.ffff}", DateTime.Now);

            s_count = stocksWithDate.Count();

            for (var i = 0; stocksWithDate.Count() > 0; ++i)
            {
                var tasks = stocksWithDate
                    .Skip(i * 100)
                    .Take(100)
                    .Select(stock => syncAsync(stock.Stock, stock.startDate, stock.endDate))
                    .ToArray();

                Task.WaitAll(tasks.Cast<Task>().ToArray());
            }

            sw.Stop();

            Console.WriteLine("end @ {0:HH:mm:ss.ffff}", DateTime.Now);
            Console.WriteLine(sw.ElapsedMilliseconds + " ms");
        }

        static long s_count = 0;

        static async Task<Tuple<Stock, IEnumerable<Quote>>> syncAsync(Stock stock, DateTime startDate, DateTime endDate)
        {
            var url = string.Format(
    "http://ichart.finance.yahoo.com/table.csv?s={0}&a={1}&b={2}&c={3}&d={4}&e={5}&f={6}&g=d&ignore=.csv",
    stock.code + "." + stock.exchange,
    (startDate.Month - 1).ToString("00"), startDate.Day, startDate.Year,
    (endDate.Month - 1).ToString("00"), endDate.Day, endDate.Year);

            Console.WriteLine("sync start {0}.{1}", stock.code, stock.exchange);

            try
            {
                var client = new WebClient();
                client.Proxy = new WebProxy("61.166.68.72", 80);
                var csv = await client.DownloadStringTaskAsync(new Uri(url));
                var quotes = await parseCsvAsync(stock, csv);

                await saveAsync(quotes);

                Console.WriteLine("sync end {0}.{1}, total {2} ------- remain ** {3}", stock.code, stock.exchange, quotes.Count(), Interlocked.Decrement(ref s_count));

                return Tuple.Create(stock, quotes);
            }
            catch (AggregateException ae)
            {
                
                var we = ae.InnerException as WebException;
                var notFound = we != null && (we.Response as HttpWebResponse) != null && (we.Response as HttpWebResponse).StatusCode == HttpStatusCode.NotFound;

                if(notFound)
                    Console.WriteLine("Not found {0}.{1} | {2}", stock.code, stock.exchange, url);
                else
                    Console.WriteLine("ex @ {0}.{1} | {2} | {3}", stock.code, stock.exchange, url, ae.Message);

                return Tuple.Create(stock, Enumerable.Empty<Quote>());
   
            }
            catch (Exception ex)
            {
                Console.WriteLine("ex @ {0}.{1} | {2} | {3}", stock.code, stock.exchange, url, ex.Message);

                return null;
            }
        }

        static Task saveAsync(IEnumerable<Quote> quotes)
        {
            return Task.Run(() =>
            {
                var sqlInsertClauses = quotes
                    .Select(p =>
                        string.Format("SELECT '{0}','{1}','{2:yyyy-MM-dd}',{3},{4},{5},{6},{7},{8}",
                        p.code, p.exchange, p.date, p.open, p.close, p.low, p.high, p.volume, p.closeAdjust));

                var sql =
                    @"declare @temp table
(
code nvarchar(100),
exchange nvarchar(100),
date date,
[open] float,
[close] float,
low float,
high float,
volume float,
closeAdjust float
PRIMARY KEY CLUSTERED(code, exchange,date)
)
INSERT INTO @temp(code,exchange,date,[open], [close], low, high, volume, closeAdjust)" + Environment.NewLine +
                    string.Join(" UNION ALL " + Environment.NewLine, sqlInsertClauses) + Environment.NewLine +
                    @"MERGE INTO hist
     USING @temp p
          ON p.code = hist.code and p.exchange = hist.exchange and p.date=hist.date
WHEN MATCHED THEN
     UPDATE SET hist.[open] = p.[open],hist.[close] = p.[close],hist.low = p.low,hist.high = p.high,hist.volume = p.volume,hist.closeAdjust = p.closeAdjust
WHEN NOT MATCHED THEN
     INSERT (code,exchange,date,[open], [close], low, high, volume, closeAdjust) VALUES(p.code,p.exchange,p.date,p.[open], p.[close], p.low, p.high, p.volume, p.closeAdjust);";

                var connString = ConfigurationManager.ConnectionStrings["trading"].ConnectionString;
                using (var conn = new SqlConnection(connString))
                {
                    conn.Open();

                    conn.Execute(sql);
                }
            });
        }

        static Task<IEnumerable<Quote>> parseCsvAsync(Stock stock, string csv)
        {
            return Task.Run<IEnumerable<Quote>>(() =>
            {
                using (var reader = new StringReader(csv))
                using (var csvReader = new CsvReader(reader))
                {
                    var quotes = new List<Quote>();
                    while (csvReader.Read())
                    {
                        var quote = new Quote
                        {
                            code = stock.code,
                            exchange = stock.exchange,
                            date = csvReader.GetField<DateTime>("Date"),
                            open = csvReader.GetField<double>("Open"),
                            high = csvReader.GetField<double>("High"),
                            low = csvReader.GetField<double>("Low"),
                            close = csvReader.GetField<double>("Close"),
                            volume = csvReader.GetField<double>("Volume"),
                            closeAdjust = csvReader.GetField<double>("Adj Close")
                        };
                        quotes.Add(quote);
                    }

                    return quotes;
                };
            });
        }
    }
}
